'''
import os
import base64
import tornado.web
import tornado.ioloop
from tornado.web import RequestHandler
from tornado.options import define, options, parse_command_line

define('port', default=8222, type=int, )


def base64_img():
    """
    获取所有的文件的base64,以列表的形式返回
    :return:
    """
    img_base64 = []
    source_path = r'image'
    img_name_list = os.listdir(source_path)
    for each_img in img_name_list:
        each_img_name = os.path.join(source_path, each_img)
        with open(each_img_name, 'rb') as f:
            base64_data = base64.b64encode(f.read())
            img_base64.append(base64_data)
    return img_base64


class Base64Handler(RequestHandler):
    def get(self):
        total_img = base64_img()
        self.render("base64.html", total_img=total_img)


if __name__ == '__main__':
    parse_command_line()
    app = tornado.web.Application(
        [(r"/base64", Base64Handler), ],
        debug=True,
        default_host="0.0.0.0",
        template_path=os.path.join(os.path.dirname(__file__), "templates")
    )
    app.listen(options.port)
    tornado.ioloop.IOLoop.instance().start()

'''

'''
from flask import Flask, Response, request, render_template
from werkzeug.utils import secure_filename
import os
  
app = Flask(__name__)
  
  
# 设置图片保存文件夹
UPLOAD_FOLDER = 'photo'
app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
  
# 设置允许上传的文件格式
ALLOW_EXTENSIOnS= ['png', 'jpg', 'jpeg']
  
  
# 判断文件后缀是否在列表中
def allowed_file(filename):
  return '.' in filename and filename.rsplit('.', 1)[-1] in ALLOW_EXTENSIONS
  
  
# 上传图片
@app.route("/photo/upload", methods=['POST', "GET"])
def uploads():
  if request.method == 'POST':
    # 获取post过来的文件名称，从name=file参数中获取
    file = request.files['file']
    if file and allowed_file(file.filename):
      print(file.filename)
      # secure_filename方法会去掉文件名中的中文
      file_name = secure_filename(file.filename)
      # 保存图片
      file.save(os.path.join(app.config['UPLOAD_FOLDER'], file_name))
      return "success"
    else:
      return "格式错误，请上传jpg格式文件"
  return render_template('index.html')
  
  

@app.route("/photo/<imageId>.jpg")
def get_frame(imageId):
    with open(r'image/{}.jpg'.format(imageId), 'rb') as f: 	
        image = f.read()
        resp = Response(image, mimetype="image/jpg")
        return resp


  
  
if __name__ == "__main__":
  app.run(host='0.0.0.0', port=8222, debug=True)

'''
'''
import os
import base64
import tornado.web
import tornado.ioloop
from tornado.web import RequestHandler
from tornado.options import define, options, parse_command_line

define('port', default=8222, type=int, )


def img_list():
    """
    获取所有的文件名，以列表的形式返回。
    :return:
    """
    source_path = r'image'
    img_name_list = os.listdir(source_path)
    return img_name_list


class PictureHandler(RequestHandler):
    def get(self):
        source_path = r'image'
        id = self.get_argument('id')
        if id.endswith('.jpg'):
            pic = open(source_path + r'/' + id, 'rb')
            pics = pic.read()
            self.write(pics)
            self.set_header("Content-type", "image/png")


class GetPictureHandler(RequestHandler):
    def get(self):
        total_img = img_list()
        self.render("show.html", total_img=total_img)


if __name__ == '__main__':
    parse_command_line()
    app = tornado.web.Application(
        [
            (r"/pic", PictureHandler),
            (r"/getPic", GetPictureHandler),
        ],
        debug=True,
        default_host="0.0.0.0",
        template_path=os.path.join(os.path.dirname(__file__), "templates")
    )
    app.listen(options.port)
    tornado.ioloop.IOLoop.instance().start()

'''
'''
from flask import Flask
from flask import Response,render_template
import os


app = Flask(__name__)
folder = './video'

# @app.route('/')
# def index():
#     return render_template('index.html')


@app.route('/video/<file_key>')
def stream_mp3(file_key):
    def generate():
        path = os.path.join(folder, file_key)
        with open(path, 'rb') as video:
            data = video.read(1024)
            while data:
                yield data
                data = video.read(1024)

    return Response(generate(), mimetype="video")




if __name__ == '__main__':
    app.run(host='0.0.0.0', port=8222)

'''

# hm_03_数据查询.py

'''
from flask import Flask
from flask_sqlalchemy import SQLAlchemy


app = Flask(__name__)

# 相关配置
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql://root:mysql@127.0.0.1:3306/test31"
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.config["SQLALCHEMY_ECHO"] = False
db = SQLAlchemy(app)

# 自定义类 继承db.Model  对应 表
class User(db.Model):
    __tablename__ = "users"  # 表名 默认使用类名的小写
    # 定义类属性 记录字段
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    email = db.Column(db.String(64))
    age = db.Column(db.Integer)

    def __repr__(self):  # 自定义 交互模式 & print() 的对象打印
        return "(%s, %s, %s, %s)" % (self.id, self.name, self.email, self.age)


@app.route('/')
def index():
    """
    查询所有用户数据


    查询有多少个用户


    查询第1个用户


    查询id为4的用户[3种方式]


    查询名字结尾字符为g的所有用户[开始 / 包含]


    查询名字和邮箱都以li开头的所有用户[2种方式]


    查询age是25 或者 `email`以`itheima.com`结尾的所有用户


    查询名字不等于wang的所有用户[2种方式]


    查询id为[1, 3, 5, 7, 9]的用户


    所有用户先按年龄从小到大, 再按id从大到小排序, 取前5个


    查询年龄从小到大第2-5位的数据


    分页查询, 每页3个, 查询第2页的数据


    查询每个年龄的人数    select age, count(name) from t_user group by age  分组聚合


    只查询所有人的姓名和邮箱  优化查询   默认使用select *


    """


    return 'index'


if __name__ == '__main__':
    # 删除所有表
    db.drop_all()
    # 创建所有表
    db.create_all()
    # 添加测试数据
    user1 = User(name='wang', email='wang@163.com', age=20)
    user2 = User(name='zhang', email='zhang@189.com', age=33)
    user3 = User(name='chen', email='chen@126.com', age=23)
    user4 = User(name='zhou', email='zhou@163.com', age=29)
    user5 = User(name='tang', email='tang@itheima.com', age=25)
    user6 = User(name='wu', email='wu@gmail.com', age=25)
    user7 = User(name='qian', email='qian@gmail.com', age=23)
    user8 = User(name='liu', email='liu@itheima.com', age=30)
    user9 = User(name='li', email='li@163.com', age=28)
    user10 = User(name='sun', email='sun@163.com', age=26)

    # 一次添加多条数据
    db.session.add_all([user1, user2, user3, user4, user5, user6, user7, user8, user9, user10])
    db.session.commit()
    app.run(debug=True)

'''

'''
from flask import Flask
from flask_restful import Api, Resource
from flask_sqlalchemy import SQLAlchemy

import pymysql
pymysql.install_as_MySQLdb()

"""
python2中数据库客户端： MySqldb
python3中数据库客户端：pymysql
解决方案一：让python2和python3的包进行转换。
import pymysql
pymysql.install_as_MySQLdb()

方案二：表示只使用python3的包，不使用python2的包
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql+pymysql://root:mysql@192.168.44.128:3306/test39"
"""

app = Flask(__name__)
db = SQLAlchemy(app)

# app.config['SQLALCHEMY_DATABASE_URI'] = "mysql://账号:密码@数据库ip地址:端口号/数据库名"
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql://root:123456@127.0.0.1:3306/zhikuxuetan"
# app.config['SQLALCHEMY_BINDS'] = {}

# 关闭数据库修改跟踪操作[提高性能]：
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# 开启输出底层执行的sql语句
app.config['SQLALCHEMY_ECHO'] = True

# 开启数据库的自动提交功能[一般不使用]
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True

@app.route('/')
def hello_word():
    return "hello, word"

if __name__ == '__main__':
    
    print(app.url_map)
    app.run(host='0.0.0.0', port= 8123, debug=True)

'''

'''
from flask_sqlalchemy import SQLAlchemy
from flask import Flask,render_template,request,flash,redirect,url_for
app=Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = "mysql://root:123456@127.0.0.1:3306/zhikuxuetan"
#创建一个SQLAlchemy类的对象。 该对象包含ORM操作的辅助函数。 它还提供了一个使用其声明用户定义模型的父级模型类。
app.config['SECRET_KEY'] = "random string"
 
 
 
db=SQLAlchemy(app)
class students(db.Model):
    id = db.Column('student_id', db.Integer, primary_key=True)
    name = db.Column(db.String(100))
    city = db.Column(db.String(50))
    addr = db.Column(db.String(200))
    pin = db.Column(db.String(10))
    def __init__(self,name,city,addr,pin):
 
 
        self.name=name
        self.city=city
        self.addr=addr
        self.pin=pin
 
 
 
@app.route('/')
def show_all():
    db.create_all()
    return render_template('show_all.html',students=students.query.all())
 
 
@app.route('/new',methods=['GET','POST'])
def new():
 
    if request.method=='POST':
        try:
            if not request.form['name']or not request.form['city']or not request.form['addr']:
                flash('Please enter all the fields','error') #如果有一个没填写，闪现消息
            else:
                student = students(request.form['name'], request.form['city'], request.form['addr'], request.form['pin'])
                print(student)
                db.session.add(student)#把数据添加到数据库
                db.session.commit()#提交数据
                flash('Record was successfully added')#闪现消息添加成功
            return  redirect(url_for('show_all')) #一个函数可以有两个return
        except Exception as e:
            pass
        
    return render_template('new.html') #点击添加按钮，渲染new.html页面
 
if __name__ == '__main__':
    db.create_all()
    app.run(debug=True)
'''
'''
from datetime import datetime

from flask import Flask
# 导入扩展包flask_sqlalchemy
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# 配置数据库的连接信息
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:123456@127.0.0.1:3306/zhikuxuetan'
# 关闭动态追踪修改的警告信息
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
# 展示sql语句
app.config['SQLALCHEMY_ECHO'] = True

# 实例化sqlalchemy对象，并且和程序实例关联
db = SQLAlchemy(app)


class User(db.Model):
    __tablename__ = 'user'

    class GENDER:
        MALE = 0
        FEMALE = 1

    id = db.Column('user_id', db.Integer, primary_key=True, doc='用户ID')
    mobile = db.Column(db.String, doc='手机号')
    password = db.Column(db.String, doc='密码')
    name = db.Column('user_name', db.String, doc='昵称')
    gender = db.Column(db.Integer, default=GENDER.FEMALE, doc='性别')
    birthday = db.Column(db.Date, doc='生日')
    is_delete = db.Column(db.Boolean, default=False, doc='是否删除')
    # 当模型类字段与表字段不一致，可在Column函数第一个参数指定
    time = db.Column('create_time', db.DateTime, default=datetime.now, doc='创建时间')
    update_time = db.Column('update_time', db.DateTime, default=datetime.now, onupdate=datetime.now, doc='更新时间')
    # primaryjoin定义连接条件 : param1:另外一方类名 param2: 具体连接条件
    follows = db.relationship('Car', primaryjoin='User.id==foreign(Car.user_id)')

@app.route('/new',methods=['GET','POST'])
class Car(db.Model):
    __tablename__ = 'car'

    class TYPE:
        SUV = 0
        SEDAN = 1
        PICKUP = 2

    id = db.Column('car_id', db.Integer, primary_key=True, doc='主键ID')
    user_id = db.Column(db.Integer, doc='用户ID')
    type = db.Column(db.Integer, doc='类型')
    name = db.Column(db.String, doc='名称')
    price = db.Column(db.Numeric, default=0.00, doc='价格')


if __name__ == '__main__':
    app.run()
'''

from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:123456@127.0.0.1:3306/zhikuxuetan'
db = SQLAlchemy(app)
class Students(db.Model):
    id = db.Column('student_id', db.Integer, primary_key = True)
    name = db.Column(db.String(100))
    city = db.Column(db.String(50))  
    addr = db.Column(db.String(200))
    pin = db.Column(db.String(10))

    def __init__(self, name, city, addr, pin):
        self.name = name
        self.city = city
        self.addr = addr
        self.pin = pin

# db.create_all()
with app.app_context():
    db.create_all()

